/* Copyright (c) 2001-2005, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package com.impetus.client.crud;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Title: Testdb Description: simple hello world db example of a standalone
 * persistent db application
 * 
 * every time it runs it adds four more rows to sample_table it does a query and
 * prints the results to standard out
 * 
 * Author: Karl Meissner karl@meissnersd.com
 */

public class RDBMSCli
{
    static Connection conn; // our connnection to the db - presist for life of
                            // program
    /** The Constant logger. */
    private static final Logger logger = LoggerFactory.getLogger(RDBMSCli.class);

    // we dont want this garbage collected until we are done
    public RDBMSCli(String db_file_name_prefix) throws Exception
    { // note more general exception

        // Load the HSQL Database Engine JDBC driver
        // hsqldb.jar should be in the class path or made part of the current
        // jar
        Class.forName("org.hsqldb.jdbcDriver");

        // connect to the database. This will load the db files and start the
        // database if it is not alread running.
        // db_file_name_prefix is used to open or create files that hold the
        // state
        // of the db.
        // It can contain directory names relative to the
        // current working directory
        conn = DriverManager.getConnection("jdbc:hsqldb:mem:" + db_file_name_prefix, // filenames
                "sa", // username
                ""); // password
        
        conn.setAutoCommit(true);
        
//        conn.setClientInfo("hsqldb.write_delay", "false");
    }

    public void shutdown() throws SQLException
    {

        Statement st = conn.createStatement();

        // db writes out to files and performs clean shuts down
        // otherwise there will be an unclean shutdown
        // when program ends
        st.execute("SHUTDOWN");
        closeConnection();
    }

    /**
     * @throws SQLException
     */
    public void closeConnection() throws SQLException
    {
        if (conn != null)
        {
            conn.close(); // if there are no other open connection
            conn = null;
        }
    }

    // use for SQL command SELECT
    public synchronized static void query(String expression) throws SQLException
    {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement(); // statement objects can be reused with

        // repeated calls to execute but we
        // choose to make a new one each time
        rs = st.executeQuery(expression); // run the query

        // do something with the result set.
        dump(rs);
        st.close(); // NOTE!! if you close a statement the associated ResultSet
                    // is

        // closed too
        // so you should copy the contents to some other object.
        // the result set is invalidated also if you recycle an Statement
        // and try to execute some other query before the result set has been
        // completely examined.
    }

    // use for SQL commands CREATE, DROP, INSERT and UPDATE
    public synchronized void update(String expression) throws SQLException
    {

        Statement st = null;

        st = conn.createStatement(); // statements

        int i = st.executeUpdate(expression); // run the query

        if (i == -1)
        {
            System.out.println("db error : " + expression);
        }
        st.close();
    } // void update()

    public static void dump(ResultSet rs) throws SQLException
    {

        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta = rs.getMetaData();
        int colmax = meta.getColumnCount();
        int i;
        Object o = null;

        // the result set is a cursor into the data. You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next();)
        {
            for (i = 0; i < colmax; ++i)
            {
                o = rs.getObject(i + 1); // Is SQL the first column is indexed

                // with 1 not 0
            }
        }
    } // void dump( ResultSet rs )

    public void createSchema(final String schemaName) throws SQLException
    {
        try
        {
            String sql = "CREATE schema " + schemaName + " AUTHORIZATION DBA";
            update(sql);
        }
        catch (Exception e)
        {
            // do nothing..
        }
        // update("set " + schemaName);
    }

    public void dropSchema(final String schemaName) throws SQLException
    {
        String sql = "drop schema " + schemaName;
        update(sql);        
    }

    public static void main(String[] args)
    {

        RDBMSCli db = null;

        try
        {
            db = new RDBMSCli("testdb");
        }
        catch (Exception ex1)
        {
        	logger.warn(ex1.getMessage());
            return;
        }
        // bye bye }

        try
        {
            
            // make an empty table // // by declaring the id column IDENTITY,
            // the db
            // will automatically // generate unique values for new rows- useful
            // for row
            // keys
            db.update("CREATE schema testdb AUTHORIZATION DBA");
            db.update("CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");
        }
        catch (SQLException ex2)
        {
        	logger.warn(ex2.getMessage());

            // ignore //ex2.printStackTrace(); // second time we run program //
            // should
            /*
             * throw execption since table // already there // // this will have
             * no effect on the db
             */}

        try
        {

            // add some rows - will create duplicates if run more then once //
            // the id
            // column is automatically generated
            db.update("INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
            db.update("INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
            db.update("INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
            db.update("INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");

            // do a query
            // db.query("SELECT * FROM sample_table WHERE num_col < 250");

            // at end of program db.shutdown(); }
        }
        catch (SQLException ex3)
        {
        	logger.warn(ex3.getMessage());
        }
    } // main()
} // class Testdb

